"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 执行switchover之后，新主机上在事务中在事务中执行批量插入,
              查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1. 执行switchover
    1. 在事务中在事务中执行批量插入
    2. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 主备切换成功 
    2. 执行成功
    3. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

PrimarySh = CommonSH('PrimaryDbUser')


@unittest.skipIf(1 == PrimarySh.get_node_num(), '非主备环境不执行')
class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.constant = Constant()
        self.table_name = 't_dbtime_test_033'
        self.function_name = 'func_dbtime_test_033'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'

    def test_sql_dbtime(self):
        step = '----step1:备机执行switchover expect:主备切换成功--'
        self.log.info(step)
        result = self.standby_sh.execute_gsctl(
            'switchover', self.constant.SWITCH_SUCCESS_MSG)
        self.assertTrue(result, '执行失败' + step)
        result = self.standby_sh.exec_refresh_conf()
        self.assertTrue(result, '执行失败' + step)
        restart_res = self.primary_sh.restart_db_cluster(True)
        flg = self.constant.START_SUCCESS_MSG in restart_res \
              or 'Degrade' in restart_res
        self.assertTrue(flg)
        self.log.info('----清理统计信息----')
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.standby_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

        step = '----step1:在事务中在事务中执行批量插入 expect:执行成功----'
        self.log.info(step)
        ddl_sql = f"create or replace function {self.function_name}" \
                  f"(tb_name varchar) return varchar is " \
                  f"begin " \
                  f"    execute immediate 'drop table if exists  ' || " \
                  f"    tb_name || ';';" \
                  f"    execute immediate 'create table  '|| tb_name || " \
                  f"    '(id int, name varchar(20));';" \
                  f"    execute immediate 'insert into ' || tb_name || " \
                  f"    ' values (generate_series(1,10), ''test'');';" \
                  f"    execute immediate 'update ' || tb_name || ' set " \
                  f"    name=''software'' where id = 3;';" \
                  f"    execute immediate 'delete from ' || tb_name || " \
                  f"    ' where id = 5;';" \
                  f"return tb_name;" \
                  f"end;"
        result = self.standby_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(self.constant.CREATE_FUNCTION_SUCCESS_MSG, result,
                         '执行失败' + step)
        transactions_sql = f"start transaction;" \
                           f"call {self.function_name}('{self.table_name}');" \
                           f"commit;"
        result = self.standby_sh.execut_db_sql(transactions_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.COMMIT_SUCCESS_MSG, result,
                      '执行失败' + step)

        step = '--step2:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'start transaction',
                    f'call {self.function_name}',
                    f'rollback']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.standby_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        step = '----清理环境----'
        self.log.info(step)
        clean_sql = f"drop table {self.table_name}; "
        result = self.standby_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        switchover_res = self.primary_sh.execute_gsctl(
            'switchover', self.constant.SWITCH_SUCCESS_MSG)
        refresh_res = self.primary_sh.exec_refresh_conf()
        restart_res = self.primary_sh.restart_db_cluster(True)
        flg = self.constant.START_SUCCESS_MSG in restart_res \
              or 'Degrade' in restart_res
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, result,
                      '执行失败' + step)
        self.assertTrue(switchover_res, '执行失败' + step)
        self.assertTrue(refresh_res, '执行失败' + step)
        self.assertTrue(flg)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
